﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.ComponentModel;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices;

namespace Shuba.Web
{
    /// <summary>
    /// Summary description for service
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class service : WebService
    {
        private static string ConnStr()
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();
            string DBServer = ConfigurationManager.AppSettings["db.server"];
            string DBName = ConfigurationManager.AppSettings["db.name"];
            string DBUser = ConfigurationManager.AppSettings["db.user"];
            string DBPass = Encryption.Decrypt(ConfigurationManager.AppSettings["db.pwd"]);
            return "Data Source = " + DBServer + "; Initial Catalog = " + DBName + "; User Id = " + DBUser + "; Password = " + DBPass + ";";
        }

        [WebMethod]
        public DateTime GetServerDateTime()
        {
            return DateTime.Now;
        }

        [WebMethod]
        public void AddLog(string NIP, string IPAddress, bool isLogin)
        {
            try
            {
                Shuba.Web.basePage basePage = new basePage();
                string AppCode = ConfigurationManager.AppSettings["app.code.spada"];
                if (isLogin)
                {
                    Shuba.Business.User user = new Shuba.Business.User();
                    Shuba.Model.UserInfo info = user.Get(NIP, AppCode);
                    info.IsLogin = true;
                    info.LoginAddr = IPAddress;
                    info.LoginDate = DateTime.Now;
                    user.Update(info);

                    basePage.AddLog("Login", String.Format("Login To {0} Application, User Name : {1}, Remote Address: {2}", AppCode, NIP, IPAddress));
                }
                else
                {
                    Shuba.Business.User user = new Shuba.Business.User();
                    Shuba.Model.UserInfo info = user.Get(NIP, AppCode);
                    info.IsLogin = false;
                    user.Update(info);

                    basePage.AddLog("Logout", String.Format("Logout From {0} Application, User Name : {1}, Remote Address: {2}", AppCode, NIP, IPAddress));
                }
            }
            catch (Exception) { }
        }

        [WebMethod]
        public DataSet GetBranchByAOCode(string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }
                    string query = "SELECT BRANCH_ID, BRANCH_NAME FROM TBL_MS_BRANCH WHERE LTRIM(RTRIM(BRANCH_ID)) = '" + UnitCode.Trim() + "' ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Branch");
                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetDocumentProduct(string updateDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }
                    string query = @" IF EXISTS (SELECT PRODUCT_ID, DOCUMENT_ID FROM TBL_DOCUMENT_PRODUCT 
                                        WHERE (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101))) " +
                                    " SELECT PRODUCT_ID, DOCUMENT_ID FROM TBL_DOCUMENT_PRODUCT ";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "DocumentProduct");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetSysParams(string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT TOP 1 ROW_ID, GU_FUP, GU_CLOSE_DEAL, PURGING_PERIOD, WEB_ROOT_ADDRESS, DB_PASS_NEW, DB_PASS_OLD, NPWP, TIME_OUT FROM TBL_SYS_PARAMS ORDER BY UPDATE_DATE DESC ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "TBL_SYS_PARAMS");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetBranchs(string updateDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT BRANCH_ID, SOURCE_CODE, BRANCH_MAPPING, BRANCH_NAME, PHONE_NO FROM TBL_MS_BRANCH WHERE LTRIM(RTRIM(BRANCH_ID)) LIKE '" + UnitCode.Trim() + "' AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Branchs");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        //jika ada yang terupdate maka download seluruh branch zip dalam satu units
        [WebMethod]
        public DataSet GetBranchZIP(string updateDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"IF EXISTS (SELECT BRANCH_ID, ZIP_CODE, KODYA_KAB, KECAMATAN, 
                                        KELURAHAN, LAYER, QUADRANT, IS_DELETED 
                                    FROM TBL_MS_BRANCH_ZIP WHERE LTRIM(RTRIM(BRANCH_ID)) 
                                        LIKE '" + UnitCode.Trim() + "' AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)))  " +
                                    " SELECT BRANCH_ID, ZIP_CODE, KODYA_KAB, KECAMATAN, " +
                                       " KELURAHAN, LAYER, QUADRANT, IS_DELETED " +
                                    " FROM TBL_MS_BRANCH_ZIP WHERE LTRIM(RTRIM(BRANCH_ID)) " +
                                       " LIKE '" + UnitCode.Trim() + "';";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "BranchZIP");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetDocuments(string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT DOCUMENT_ID, DOCUMENT_NAME FROM TBL_MS_DOCUMENTS WHERE IS_ACTIVE = '1' ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Documents");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProducts(string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE FROM TBL_MS_PRODUCTS WHERE (IS_ACTIVE = '1') ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Products");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetChannel(string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT [CHANNEL_ID],[CHANNEL_NAME], [IS_ACTIVE] FROM [TBL_MS_CHANNEL];";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "CHANNEL");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetStandard(string updateDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT MS_STD_ID, MS_STD_GRP_ID, MS_STD_NAME, IS_ACTIVE, MS_STD_TYPE FROM TBL_MS_STANDARD WHERE UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Standard");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetUsers(string unitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT USER_ID, LTRIM(RTRIM(AO_CODE)) AS AO_CODE, NIP, BRANCH_ID, USER_NAME, USER_TYPE, EMAIL_ADDRESS, MAC_ADDRESS, OLD_MAC_ADDRESS, IS_MIGRATION FROM V_USER WHERE (LTRIM(RTRIM(BRANCH_ID)) LIKE '" + unitCode.Trim() + "') AND (APP_ID LIKE '" + SPADAAppCode.Trim() + "') ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Users");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetUsersCOAssgined(string unitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT USER_ID, LTRIM(RTRIM(AO_CODE)) AS AO_CODE, NIP, BRANCH_ID, " +
                                        " USER_NAME, USER_TYPE, EMAIL_ADDRESS, MAC_ADDRESS, OLD_MAC_ADDRESS " +
                                    " FROM V_USER WHERE (LTRIM(RTRIM(BRANCH_ID)) LIKE '" + unitCode.Trim() + "') " +
                                        " AND (LTRIM(RTRIM(USER_TYPE)) ='CO') " +
                                    " GROUP BY USER_ID, AO_CODE, NIP, BRANCH_ID, USER_NAME, USER_TYPE, EMAIL_ADDRESS, MAC_ADDRESS, OLD_MAC_ADDRESS ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    //sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "UsersCO");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetTopUp(string UnitCode, string updateDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"
                      SELECT
                        CIF_NUMBER, LOAN_NUMBER, UNIT_CODE, DEBITOR_NAME, PRODUCT_CODE, CURRENT_OS, CURRENT_INSTALLMENT, OFFER_PLAFON, 
                        OFFER_INSTALLMENT, OFFER_INTEREST, OFFER_TENOR, OFFER_FRESH_MONEY, PRIORITY, JENIS_USAHA, KODYA_KAB_BUSINESS, 
                        KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ZIP_CODE_BUSINESS, ADDRESS_BUSINESS, MOBILE_PHONE, OFFICE_PHONE, 
                        RESIDENTIAL_PHONE, DATE_VISIT_PLAN, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING
                    FROM TBL_TOPUP WHERE (UNIT_CODE LIKE '" + UnitCode.Trim() + "') AND (IS_DOWNLOADED = '0') AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "TopUp");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetPipelineRegular(string UnitCode, string updateDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"
                      SELECT
                        [ROW_ID],[UNIT_CODE],[FULL_NAME],[JENIS_USAHA],[RENCANA_PLAFON],[MOBILE_PHONE]
                        ,[OFFICE_PHONE],[RESIDENTIAL_PHONE],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS],[KELURAHAN_BUSINESS]
                        ,[ADDRESS_BUSINESS],[ZIP_CODE_BUSINESS],[DATE_VISIT_PLAN],[PROSPECT_TYPE],[DEBTOR_TYPE]
                        ,[CHANNELING],[OMZET_1],[OMZET_2],[OMZET_3],[OMZET_4]
                        ,[OMZET_5],[OMZET_6],[OMZET_7],[OMZET_8],[OMZET_9]
                        ,[OMZET_10],[OMZET_11],[OMZET_12],[OMZET_AVERAGE]
                        ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE]
                    FROM TBL_PIPELINE WHERE (UNIT_CODE LIKE '" + UnitCode.Trim() + "') AND (IS_DOWNLOADED = '0') AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Pipeline");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public bool UpdatePipelineRegular(List<string> DOWNLOAD_BY, List<DateTime> DOWNLOAD_DATE, List<string> ROW_ID, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlCommand sqlCM = null;

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    for (int i = 0; i < DOWNLOAD_BY.Count; i++)
                    {
                        string query = "UPDATE TBL_PIPELINE SET IS_DOWNLOADED = 1, DOWNLOAD_BY=@DOWNLOAD_BY, DOWNLOAD_DATE=@DOWNLOAD_DATE WHERE (ROW_ID LIKE '' + @ROW_ID + '') ;";
                        sqlCM = new SqlCommand(query, sqlCon);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_BY", DOWNLOAD_BY[i]);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_DATE", DOWNLOAD_DATE[i]);
                        sqlCM.Parameters.AddWithValue("@ROW_ID", ROW_ID[i]);
                        sqlCM.ExecuteNonQuery();
                    }
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (sqlCM != null)
                    {
                        sqlCM.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return false;
            }
        }

        [WebMethod]
        public bool UpdateTopUpList(List<string> downloadBy, List<DateTime> downloadDate, List<string> cifNumber, List<string> loanNumber, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlCommand sqlCM = null;

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    for (int i = 0; i < downloadBy.Count; i++)
                    {
                        string query = "UPDATE TBL_TOPUP SET IS_DOWNLOADED = 1, DOWNLOAD_BY=@DOWNLOAD_BY, DOWNLOAD_DATE=@DOWNLOAD_DATE WHERE (CIF_NUMBER LIKE '' + @CIF_NUMBER + '') AND (LOAN_NUMBER LIKE '' + @LOAN_NUMBER + '') ;";
                        sqlCM = new SqlCommand(query, sqlCon);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_BY", downloadBy[i]);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_DATE", downloadDate[i]);
                        sqlCM.Parameters.AddWithValue("@CIF_NUMBER", cifNumber[i]);
                        sqlCM.Parameters.AddWithValue("@LOAN_NUMBER", loanNumber[i]);
                        sqlCM.ExecuteNonQuery();
                    }
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (sqlCM != null)
                    {
                        sqlCM.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return false;
            }
        }

        [WebMethod]
        public bool UpdateProspectRefferal(List<string> downloadBy, List<DateTime> downloadDate, List<string> ReffID, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlCommand sqlCM = null;

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    for (int i = 0; i < downloadBy.Count; i++)
                    {
                        string query = "UPDATE TBL_PROSPECT_REFFERAL SET IS_DOWNLOADED = 1, DOWNLOAD_BY=@DOWNLOAD_BY, DOWNLOAD_DATE=@DOWNLOAD_DATE WHERE (REFFERAL_ID LIKE '' + @REFF_ID + '') ;";
                        sqlCM = new SqlCommand(query, sqlCon);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_BY", downloadBy[i]);
                        sqlCM.Parameters.AddWithValue("@DOWNLOAD_DATE", downloadDate[i]);
                        sqlCM.Parameters.AddWithValue("@REFF_ID", ReffID[i]);
                        sqlCM.ExecuteNonQuery();
                    }

                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    if (sqlCM != null)
                    {
                        sqlCM.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return false;
            }
        }

        [WebMethod]
        public DataSet GetProspectRefferal(string updateDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"
                    SELECT 
                        REFFERAL_ID, FULL_NAME, JENIS_USAHA, OFFICE_PHONE, RENCANA_PLAFON, ADDRESS_BUSINESS, UNIT_CODE_SOURCE, UNIT_CODE_DEST, 
                        AO_CODE_ORIG, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, UPLOAD_BY, UPLOAD_DATE 
                    FROM TBL_PROSPECT_REFFERAL WHERE (LTRIM(RTRIM(UNIT_CODE_DEST)) LIKE '" + UnitCode.Trim() + "') AND (IS_DOWNLOADED = '0') AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectRefferal");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProspects(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query =
                        @" SELECT * FROM TBL_PROSPECT 
                            WHERE (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                            "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "') ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "Prospect");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProspectVisit(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_PROSPECT_VISIT WHERE PROSPECT_ID IN 
                                    (
                                    SELECT PROSPECT_ID FROM TBL_PROSPECT 
                                    WHERE 
                                        (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "')) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectVisit");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public int GetCountProspect(string nip)
        {
            SqlConnection sqlCon = null;
            SqlCommand sqlCmd = null;

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query =
                    @" DECLARE @UNIT_CODE VARCHAR(6) ;
                           SELECT TOP 1 @UNIT_CODE = BRANCH_ID FROM V_USER WHERE NIP = '" + nip + "' ; " +
                      "SELECT ISNULL(COUNT(*),0) FROM TBL_PROSPECT WHERE (LTRIM(RTRIM(UNIT_CODE)) LIKE @UNIT_CODE) ";
                sqlCmd = new SqlCommand(query, sqlCon);
                return Convert.ToInt32(sqlCmd.ExecuteScalar());
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlCmd != null)
                {
                    sqlCmd.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        [WebMethod]
        public DataSet GetProspectAddresses(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_PROSPECT_ADDRESS WHERE PROSPECT_ID IN 
                                    (
                                    SELECT PROSPECT_ID FROM TBL_PROSPECT 
                                    WHERE 
                                        (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "')) ;";

                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectAddress");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProspectBankHistory(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_PROSPECT_BANK_HISTORY WHERE PROSPECT_ID IN 
                                    (
                                    SELECT PROSPECT_ID FROM TBL_PROSPECT 
                                    WHERE 
                                        (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "')) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectBankHistory");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProspectDocuments(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_PROSPECT_DOCUMENTS WHERE PROSPECT_ID IN 
                                    (
                                    SELECT PROSPECT_ID FROM TBL_PROSPECT 
                                    WHERE 
                                        (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "')) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectDocuments");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetProspectEmergencyContact(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_PROSPECT_EMG_CONTACT WHERE PROSPECT_ID IN 
                                    (
                                    SELECT PROSPECT_ID FROM TBL_PROSPECT 
                                    WHERE 
                                        (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "')) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "ProspectEmgContact");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetMaintenanceVisit(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_MAINTENANCE_VISIT WHERE  (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "') ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "MaintenanceVisit");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetSOTargetPlan(DateTime startDate, DateTime endDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = @"SELECT * FROM TBL_SO_TARGET_PLAN WHERE  (LTRIM(RTRIM(UNIT_CODE)) LIKE '" + UnitCode.Trim() + "') " +
                                        "AND (CONVERT(DATETIME, CAST(UPDATE_DATE AS VARCHAR(11)), 101) BETWEEN '" + startDate.ToString("yyyy-MM-dd") + "' AND '" + endDate.ToString("yyyy-MM-dd") +
                                     "') ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "SoTargetPlan");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetBranchNIPSO(string updateDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();
                UnitCode = UnitCode.Trim();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT BRANCH_ID, AO_CODE, NIP FROM TBL_MS_BRANCH_NIP_SO WHERE (LTRIM(RTRIM(BRANCH_ID)) LIKE '" + UnitCode + "') AND (IS_DOWNLOADED = '0') AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "BranchNIPSO");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public bool CheckChannelRelationship(string prospectType, string debtor_type, string channeling)
        {
            SqlConnection sqlCon = null;
            SqlCommand sqlCM = null;

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = @"SELECT ISNULL(COUNT(*),0) FROM TBL_MS_CHANNEL_MAPPING WHERE IS_ACTIVE = '1' AND (LTRIM(RTRIM(CHANNEL_ID)) = '" + channeling.Trim() + "') AND (LTRIM(RTRIM(PROSPECT_TYPE)) = '" + prospectType.Trim() + "') AND (LTRIM(RTRIM(DEBTOR_TYPE)) = '" + debtor_type.Trim() + "') ";
                sqlCM = new SqlCommand(query, sqlCon);

                if (Convert.ToInt32(sqlCM.ExecuteScalar()) > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                if (sqlCM != null)
                {
                    sqlCM.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        [WebMethod]
        public string GeneratePRNNew(string unitCode, string segmentCode, string prospectType, string debtor_type, string channeling, string CustName, string IsNegativeChecking)
        {
            SqlConnection sqlCon = null;
            SqlCommand sqlCM = null;

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                if (CheckChannelRelationship(prospectType, debtor_type, channeling))
                {
                    sqlCM = new SqlCommand("GENERATE_PRN_NEW", sqlCon);
                    sqlCM.CommandType = CommandType.StoredProcedure;
                    SqlParameter paramUnitCode = new SqlParameter("@UNIT_CODE", SqlDbType.VarChar) { Value = unitCode };
                    sqlCM.Parameters.Add(paramUnitCode);

                    SqlParameter paramSegmentCode = new SqlParameter("@SEGMENT_CODE", SqlDbType.VarChar) { Value = segmentCode };
                    sqlCM.Parameters.Add(paramSegmentCode);

                    SqlParameter paramProspectType = new SqlParameter("@PROSPECT_TYPE", SqlDbType.VarChar) { Value = prospectType };
                    sqlCM.Parameters.Add(paramProspectType);

                    SqlParameter paramDebtorType = new SqlParameter("@DEBTOR_TYPE", SqlDbType.VarChar) { Value = debtor_type };
                    sqlCM.Parameters.Add(paramDebtorType);

                    SqlParameter paramChannelID = new SqlParameter("@CHANNEL_ID", SqlDbType.VarChar) { Value = channeling };
                    sqlCM.Parameters.Add(paramChannelID);

                    SqlParameter paramCustName = new SqlParameter("@NAM_CUST", SqlDbType.VarChar) { Value = CustName };
                    sqlCM.Parameters.Add(paramCustName);

                    SqlParameter paramIsNegativeCheck = new SqlParameter("@IS_CHECK_NEGATIVE_FILE", SqlDbType.VarChar) { Value = IsNegativeChecking.Trim() };
                    sqlCM.Parameters.Add(paramIsNegativeCheck);

                    return sqlCM.ExecuteScalar().ToString();
                }
                else
                {
                    return "~,0";
                }
            }
            catch
            {
                return string.Empty;
            }
            finally
            {
                if (sqlCM != null)
                {
                    sqlCM.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        [WebMethod]
        public string CheckDuplicateData(string FullName, string BusinessAddress)
        {
            SqlConnection sqlCon = null;
            SqlCommand sqlCM = null;

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }
                sqlCM = new SqlCommand("CHECK_DEDUP_DATA", sqlCon);
                sqlCM.CommandType = CommandType.StoredProcedure;
                SqlParameter paramFullName = new SqlParameter("@FULL_NAME", SqlDbType.VarChar) { Value = FullName };
                sqlCM.Parameters.Add(paramFullName);

                SqlParameter paramBusinessAddress = new SqlParameter("@ADDRESS", SqlDbType.VarChar) { Value = BusinessAddress };
                sqlCM.Parameters.Add(paramBusinessAddress);

                return sqlCM.ExecuteScalar().ToString();
            }
            catch
            {
                return string.Empty;
            }
            finally
            {
                if (sqlCM != null)
                {
                    sqlCM.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        static bool IsLDAPUserNew(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true; //FOR TESTING PURPOSE ONLY

            string LDAPServer = ConfigurationManager.AppSettings["ldap"];

            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil
                return true;
            }
            catch
            {
                return false;
            }
        }

        //FOR OLD SPADA
        static bool IsLDAPUser(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true; //FOR TESTING PURPOSE ONLY

            string LDAPServer = ConfigurationManager.AppSettings["ldap"];

            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil

                return true;
            }
            catch
            {
                return false;
            }
        }

        static bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
            /*
            string LDAPServer = ConfigurationManager.AppSettings["ldap"];
            
            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil

                return true;
            }
            catch
            {
                return false;
            }
            */
        }

        [WebMethod]
        public string CheckLDAPSPADAUserNew(string LoginUser, string LoginPass)
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string UserUnitCode = "~";
            string SPADADBEncCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);

            if (IsLDAPUserNew(LoginUser, LoginPass))
            {
                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                SqlConnection sqlCon = null;
                SqlCommand sqlCM = null;

                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = "SELECT TOP 1 LTRIM(RTRIM(BRANCH_ID)) FROM V_USER WHERE (LTRIM(RTRIM(APP_ID)) LIKE '" + SPADAAppCode.Trim() + "') AND (LTRIM(RTRIM(NIP)) LIKE '" + LoginUser.Trim() + "') ;";
                sqlCM = new SqlCommand(query, sqlCon);
                sqlCM.CommandType = CommandType.Text;

                UserUnitCode = sqlCM.ExecuteScalar().ToString() + "," + SPADADBEncCode;

                sqlCM.Dispose();
                sqlCon.Close();
                sqlCon.Dispose();

                return UserUnitCode;
            }
            else
            {
                return UserUnitCode + "," + SPADADBEncCode;
            }
        }

        //FOR SPADA OLD
        [WebMethod]
        public string CheckLDAPSPADAUser(string LoginUser, string LoginPass)
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string UserUnitCode = "~";
            string SPADADBEncCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);

            if (IsLDAPUser(LoginUser, LoginPass))
            {
                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                SqlConnection sqlCon = null;
                SqlCommand sqlCM = null;

                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = "SELECT TOP 1 LTRIM(RTRIM(BRANCH_ID)) FROM V_USER WHERE (LTRIM(RTRIM(APP_ID)) LIKE '" + SPADAAppCode.Trim() + "') AND (LTRIM(RTRIM(NIP)) LIKE '" + LoginUser.Trim() + "') ;";
                sqlCM = new SqlCommand(query, sqlCon);
                sqlCM.CommandType = CommandType.Text;

                UserUnitCode = sqlCM.ExecuteScalar().ToString() + "," + SPADADBEncCode;

                sqlCM.Dispose();
                sqlCon.Close();
                sqlCon.Dispose();

                return UserUnitCode;
            }
            else
            {
                return UserUnitCode + "," + SPADADBEncCode;
            }
        }

        [WebMethod]
        public DataSet GetSPADAUserMenu(string LoginUser, string LoginPass, string UnitCode)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "EXEC GET_SPADA_USER_MENU '" + SPADAAppCode.Trim() + "', '" + LoginUser.Trim() + "', '" + UnitCode.Trim() + "'";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "SpadaUserMenu");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetSPADAMainMenu(string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "EXEC GET_SPADA_USER_MAIN_MENU '" + SPADAAppCode.Trim() + "'";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "SpadaMainMenu");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public DataSet GetSpadaUpdate()
        {
            SqlConnection sqlCon = null;
            SqlDataAdapter sqlDA = null;
            DataSet ds = new DataSet();

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = @"SELECT [DATE_FILE_UPLOAD],[DATE_FILE_MODIFIED]
                                      ,[FILE_SIZE_UPLOAD],[PATH_FILE_UPLOAD]
                                      ,[NAME_FOLDER_UPLOAD],[FILE_NAME_UPLOAD]
                                      ,[ENTRY_BY],[ENTRY_DATE]
                                      ,[UPDATE_BY],[UPDATE_DATE]
                                FROM TBL_MS_SPADA_FILE_VERSION
                                WHERE [UPDATE_DATE] = (SELECT MAX([UPDATE_DATE]) 
                                                        FROM TBL_MS_SPADA_FILE_VERSION AS F 
                                                        WHERE SUBSTRING(F.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', F.[FILE_NAME_UPLOAD]) - 1) = SUBSTRING(TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD]) - 1));";
                sqlDA = new SqlDataAdapter(query, sqlCon);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDA.Fill(ds, "TBL_MS_SPADA_FILE_VERSION");

                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDA != null)
                {
                    sqlDA.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        [WebMethod]
        public DataSet GetUpdateTableSpada(string CreateDate)
        {
            SqlConnection sqlCon = null;
            SqlDataAdapter sqlDA = null;
            DataSet ds = new DataSet();

            try
            {
                sqlCon = new SqlConnection(ConnStr());
                if (sqlCon.State == ConnectionState.Closed)
                {
                    sqlCon.Open();
                }

                string query = @"SELECT [TABLE_NAME]
                                      ,[FIELD_NAME]
                                      ,[DATA_TYPE]
                                      ,[IS_TABLE_NEW]
                                      ,[ENTRY_DATE]
                                  FROM [TBL_UPDATE_TBL_SPADA] WHERE (ENTRY_DATE > DATEADD(MONTH, -1, CONVERT(DATETIME,  '" + CreateDate + "', 101)))";
                sqlDA = new SqlDataAdapter(query, sqlCon);
                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                sqlDA.Fill(ds, "TBL_MS_SPADA_FILE_VERSION");

                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDA != null)
                {
                    sqlDA.Dispose();
                }
                if (sqlCon != null)
                {
                    sqlCon.Close();
                    sqlCon.Dispose();
                }
            }
        }

        [WebMethod]
        public DataSet GetBranchTargetPlan(string updateDate, string UnitCode, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                SqlConnection sqlCon = null;
                SqlDataAdapter sqlDA = null;
                DataSet ds = new DataSet();

                try
                {
                    sqlCon = new SqlConnection(ConnStr());
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }

                    string query = "SELECT BRANCH_ID, TARGET_YEAR, TARGET_AMOUNT, NOA FROM TBL_BRANCH_TARGET_PLAN WHERE LTRIM(RTRIM(BRANCH_ID)) LIKE '" + UnitCode.Trim() + "' AND (UPDATE_DATE > CONVERT(DATETIME,  '" + updateDate + "', 101)) ;";
                    sqlDA = new SqlDataAdapter(query, sqlCon);
                    sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    sqlDA.Fill(ds, "BranchTargetPlan");

                    return ds;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (sqlDA != null)
                    {
                        sqlDA.Dispose();
                    }
                    if (sqlCon != null)
                    {
                        sqlCon.Close();
                        sqlCon.Dispose();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Shuba.Model.ProspectInfo _ProspectInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectInfo = new Shuba.Model.ProspectInfo();

                        _ProspectInfo.PROSPECT_ID = dr["PROSPECT_ID"].ToString();
                        _ProspectInfo.PROSPECT_NO = dr["PROSPECT_NO"].ToString();
                        _ProspectInfo.UNIT_CODE = dr["UNIT_CODE"].ToString();
                        _ProspectInfo.SOURCE_CODE = dr["SOURCE_CODE"].ToString();
                        _ProspectInfo.AO_CODE = dr["AO_CODE"].ToString();
                        _ProspectInfo.FULL_NAME = dr["FULL_NAME"].ToString();
                        _ProspectInfo.KTP_NAME = dr["KTP_NAME"].ToString();
                        _ProspectInfo.KTP_NO = dr["KTP_NO"].ToString();
                        _ProspectInfo.DATE_OF_BIRTH = Convert.ToDateTime(dr["DATE_OF_BIRTH"]);
                        _ProspectInfo.PLACE_OF_BIRTH = dr["PLACE_OF_BIRTH"].ToString();
                        _ProspectInfo.MOTHER_NAME = dr["MOTHER_NAME"].ToString();
                        _ProspectInfo.MALE_FEMALE = dr["MALE_FEMALE"].ToString();
                        _ProspectInfo.MARITAL_STATUS = dr["MARITAL_STATUS"].ToString();
                        _ProspectInfo.SPOUSE_NAME = dr["SPOUSE_NAME"].ToString();
                        _ProspectInfo.SPOUSE_DATE_OF_BIRTH = Convert.ToDateTime(dr["SPOUSE_DATE_OF_BIRTH"]);
                        _ProspectInfo.NPWP_NO = dr["NPWP_NO"].ToString();
                        _ProspectInfo.KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString();
                        _ProspectInfo.KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString();
                        _ProspectInfo.KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString();
                        _ProspectInfo.ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString();
                        _ProspectInfo.ADDRESS_BUSINESS = dr["ADDRESS_BUSINESS"].ToString();
                        _ProspectInfo.RESIDENTIAL_STATUS = dr["RESIDENTIAL_STATUS"].ToString();
                        _ProspectInfo.RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString();
                        _ProspectInfo.MOBILE_PHONE = dr["MOBILE_PHONE"].ToString();
                        _ProspectInfo.OFFICE_STATUS = dr["OFFICE_STATUS"].ToString();
                        _ProspectInfo.OFFICE_PHONE = dr["OFFICE_PHONE"].ToString();
                        _ProspectInfo.YEAR_OF_STAY = Convert.ToInt16(dr["YEAR_OF_STAY"]);
                        _ProspectInfo.JML_TANGGUNGAN = Convert.ToInt16(dr["JML_TANGGUNGAN"]);
                        _ProspectInfo.JENIS_USAHA = dr["JENIS_USAHA"].ToString();
                        _ProspectInfo.LAMA_USAHA = dr["LAMA_USAHA"].ToString();
                        _ProspectInfo.OMSET_PER_HARI = Convert.ToInt32(dr["OMSET_PER_HARI"]);
                        _ProspectInfo.ADA_REK_BANK = dr["ADA_REK_BANK"].ToString();
                        _ProspectInfo.RENCANA_PLAFON = Convert.ToDouble(dr["RENCANA_PLAFON"]);
                        _ProspectInfo.SALES_CHANNEL = dr["SALES_CHANNEL"].ToString();
                        _ProspectInfo.PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString();
                        _ProspectInfo.FUNDING_LENDING = dr["FUNDING_LENDING"].ToString();
                        _ProspectInfo.RESTRUCT_COMPONENT = dr["RESTRUCT_COMPONENT"].ToString();
                        _ProspectInfo.PROGRESS_STATUS = dr["PROGRESS_STATUS"].ToString();
                        _ProspectInfo.CYCLE_STATUS = dr["CYCLE_STATUS"].ToString();
                        _ProspectInfo.INSTALLMENT = Convert.ToDouble(dr["INSTALLMENT"]);
                        _ProspectInfo.FAP_DATE = Convert.ToDateTime(dr["FAP_DATE"]);
                        _ProspectInfo.NO_OF_CALLS = Convert.ToInt16(dr["NO_OF_CALLS"]);
                        _ProspectInfo.IS_INTERESTED = dr["IS_INTERESTED"].ToString();
                        _ProspectInfo.NOT_INTERESTED_REASON = dr["NOT_INTERESTED_REASON"].ToString();
                        _ProspectInfo.NOT_INTERESTED_NOTE = dr["NOT_INTERESTED_NOTE"].ToString();
                        _ProspectInfo.KET_KENDALA = dr["KET_KENDALA"].ToString();
                        _ProspectInfo.SCM_NOTE = dr["SCM_NOTE"].ToString();
                        _ProspectInfo.PRODUCT_TYPE = Convert.ToInt16(dr["PRODUCT_TYPE"]);
                        _ProspectInfo.CIF_NUMBER = Convert.ToInt32(dr["CIF_NUMBER"]);
                        _ProspectInfo.KARAKTER = dr["KARAKTER"].ToString();
                        _ProspectInfo.REPUTASI_DEBITUR = dr["REPUTASI_DEBITUR"].ToString();
                        _ProspectInfo.RIWAYAT_DSP = dr["RIWAYAT_DSP"].ToString();
                        _ProspectInfo.TAKSIRAN_PLAFON = Convert.ToDouble(dr["TAKSIRAN_PLAFON"]);
                        _ProspectInfo.TAKSIRAN_USAHA = dr["TAKSIRAN_USAHA"].ToString();
                        _ProspectInfo.IS_DOCUMENT_COMPLETED = dr["IS_DOCUMENT_COMPLETED"].ToString();
                        _ProspectInfo.UM_DECISION = dr["UM_DECISION"].ToString();
                        _ProspectInfo.UM_DECISION_NOTE = dr["UM_DECISION_NOTE"].ToString();
                        _ProspectInfo.UM_DECISION_DATE = Convert.ToDateTime(dr["UM_DECISION_DATE"]);
                        _ProspectInfo.LOAN_NUMBER = dr["LOAN_NUMBER"].ToString();
                        _ProspectInfo.PRODUCT_CODE = dr["PRODUCT_CODE"].ToString();
                        _ProspectInfo.CURRENT_OS = Convert.ToDouble(dr["CURRENT_OS"]);
                        _ProspectInfo.CURRENT_INSTALLMENT = Convert.ToDouble(dr["CURRENT_INSTALLMENT"]);
                        _ProspectInfo.OFFER_PLAFON = Convert.ToDouble(dr["OFFER_PLAFON"]);
                        _ProspectInfo.OFFER_INSTALLMENT = Convert.ToDouble(dr["OFFER_INSTALLMENT"]);
                        _ProspectInfo.OFFER_INTEREST = dr["OFFER_INTEREST"].ToString();
                        _ProspectInfo.OFFER_TENOR = Convert.ToInt16(dr["OFFER_TENOR"]);
                        _ProspectInfo.OFFER_FRESH_MONEY = Convert.ToDouble(dr["OFFER_FRESH_MONEY"]);
                        _ProspectInfo.PRIORITY = dr["PRIORITY"].ToString();
                        _ProspectInfo.PIPELINE_START_TIME = Convert.ToDateTime(dr["PIPELINE_START_TIME"]);
                        _ProspectInfo.PIPELINE_FINISH_TIME = Convert.ToDateTime(dr["PIPELINE_FINISH_TIME"]);
                        _ProspectInfo.PROSPECT_START_TIME = Convert.ToDateTime(dr["PROSPECT_START_TIME"]);
                        _ProspectInfo.PROSPECT_FINISH_TIME = Convert.ToDateTime(dr["PROSPECT_FINISH_TIME"]);
                        _ProspectInfo.BI_GOOD_UNTIL = Convert.ToDateTime(dr["BI_GOOD_UNTIL"]);
                        _ProspectInfo.IS_BI_CHECKING = dr["IS_BI_CHECKING"].ToString() == string.Empty ? "0" : dr["IS_BI_CHECKING"].ToString();
                        _ProspectInfo.IS_OFFLINE_ENTRY = dr["IS_OFFLINE_ENTRY"].ToString();
                        _ProspectInfo.ENTRY_BY = dr["ENTRY_BY"].ToString();
                        _ProspectInfo.ENTRY_DATE = Convert.ToDateTime(dr["ENTRY_DATE"]);
                        _ProspectInfo.UPDATE_BY = dr["UPDATE_BY"].ToString();
                        _ProspectInfo.UPDATE_DATE = Convert.ToDateTime(dr["UPDATE_DATE"]);
                        _ProspectInfo.IS_BY_UPLOAD = dr["IS_BY_UPLOAD"].ToString();
                        _ProspectInfo.UPLOAD_BY = UploadBy;
                        _ProspectInfo.UPLOAD_DATE = UploadDate;
                        _ProspectInfo.IS_DELETED = dr["IS_DELETED"].ToString();
                        _ProspectInfo.DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString();
                        _ProspectInfo.GU_FUP = Convert.ToDateTime(dr["GU_FUP"]);
                        _ProspectInfo.GU_CLOSE_DEAL = Convert.ToDateTime(dr["GU_CLOSE_DEAL"]);
                        _ProspectInfo.IS_DEDUP = dr["IS_DEDUP"].ToString();
                        _ProspectInfo.CO_ASSIGNED = dr["CO_ASSIGNED"].ToString();
                        _ProspectInfo.CHANNELING = dr["CHANNELING"].ToString();
                        _ProspectInfo.OMZET_1 = Convert.ToDouble(dr["OMZET_1"]);
                        _ProspectInfo.OMZET_2 = Convert.ToDouble(dr["OMZET_2"]);
                        _ProspectInfo.OMZET_3 = Convert.ToDouble(dr["OMZET_3"]);
                        _ProspectInfo.OMZET_4 = Convert.ToDouble(dr["OMZET_4"]);
                        _ProspectInfo.OMZET_5 = Convert.ToDouble(dr["OMZET_5"]);
                        _ProspectInfo.OMZET_6 = Convert.ToDouble(dr["OMZET_6"]);
                        _ProspectInfo.OMZET_7 = Convert.ToDouble(dr["OMZET_7"]);
                        _ProspectInfo.OMZET_8 = Convert.ToDouble(dr["OMZET_8"]);
                        _ProspectInfo.OMZET_9 = Convert.ToDouble(dr["OMZET_9"]);
                        _ProspectInfo.OMZET_10 = Convert.ToDouble(dr["OMZET_10"]);
                        _ProspectInfo.OMZET_11 = Convert.ToDouble(dr["OMZET_11"]);
                        _ProspectInfo.OMZET_12 = Convert.ToDouble(dr["OMZET_12"]);
                        _ProspectInfo.OMZET_AVERAGE = Convert.ToDouble(dr["OMZET_AVERAGE"]);
                        _ProspectInfo.IS_PIPELINE_KP = dr["IS_PIPELINE_KP"].ToString();
                        _Prospect.SaveProspect(_ProspectInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_ADDRESS(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                Shuba.Model.ProspectAddressInfo _ProspectAddressInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectAddressInfo = new Shuba.Model.ProspectAddressInfo();

                        _ProspectAddressInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _ProspectAddressInfo.ADDRESS_TYPE = theRow["ADDRESS_TYPE"].ToString();
                        _ProspectAddressInfo.KODYA_KAB = theRow["KODYA_KAB"].ToString();
                        _ProspectAddressInfo.KECAMATAN = theRow["KECAMATAN"].ToString();
                        _ProspectAddressInfo.KELURAHAN = theRow["KELURAHAN"].ToString();
                        _ProspectAddressInfo.ZIP_CODE = theRow["ZIP_CODE"].ToString();
                        _ProspectAddressInfo.ADDRESS = theRow["ADDRESS"].ToString();
                        _ProspectAddressInfo.ADDRESS_RT = theRow["ADDRESS_RT"].ToString();
                        _ProspectAddressInfo.ADDRESS_RW = theRow["ADDRESS_RW"].ToString();
                        _ProspectAddressInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectAddressInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectAddressInfo.UPDATE_BY = UploadBy;
                        _ProspectAddressInfo.UPDATE_DATE = UploadDate;
                        _ProspectAddressInfo.UPLOAD_BY = theRow["UPLOAD_BY"].ToString();
                        _ProspectAddressInfo.UPLOAD_DATE = Convert.ToDateTime(theRow["UPLOAD_DATE"]);

                        _Prospect.SaveProspectAddress(_ProspectAddressInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Address " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_BANK_HISTORY(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                Shuba.Model.ProspectBankHistoryInfo _ProspectBankHistoryInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectBankHistoryInfo = new Shuba.Model.ProspectBankHistoryInfo();

                        _ProspectBankHistoryInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _ProspectBankHistoryInfo.BANK_NAME = theRow["BANK_NAME"].ToString();
                        _ProspectBankHistoryInfo.SIMPAN_PINJAM = theRow["SIMPAN_PINJAM"].ToString();
                        _ProspectBankHistoryInfo.PLAFON = Convert.ToDouble(theRow["PLAFON"]);
                        _ProspectBankHistoryInfo.JANGKA_BULAN = Convert.ToInt16(theRow["JANGKA_BULAN"]);
                        _ProspectBankHistoryInfo.MATURITY_DATE = Convert.ToDateTime(theRow["MATURITY_DATE"]);
                        _ProspectBankHistoryInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectBankHistoryInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectBankHistoryInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _ProspectBankHistoryInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _ProspectBankHistoryInfo.UPLOAD_BY = UploadBy;
                        _ProspectBankHistoryInfo.UPLOAD_DATE = UploadDate;

                        _Prospect.SaveProspectBankHistory(_ProspectBankHistoryInfo);
                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Bank History " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_DOCUMENTS(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                Shuba.Model.ProspectDocumentInfo _ProspectDocumentInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectDocumentInfo = new Shuba.Model.ProspectDocumentInfo();

                        _ProspectDocumentInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _ProspectDocumentInfo.DOCUMENT_ID = Convert.ToInt16(theRow["DOCUMENT_ID"]);
                        _ProspectDocumentInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectDocumentInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectDocumentInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _ProspectDocumentInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _ProspectDocumentInfo.UPLOAD_BY = UploadBy;
                        _ProspectDocumentInfo.UPLOAD_DATE = UploadDate;

                        _Prospect.SaveProspectDocument(_ProspectDocumentInfo);
                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Documents " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_EMG_CONTACT(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                Shuba.Model.ProspectEmgContactInfo _ProspectEmgContactInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectEmgContactInfo = new Shuba.Model.ProspectEmgContactInfo();

                        _ProspectEmgContactInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _ProspectEmgContactInfo.EMG_CONTACT_ID = Convert.ToByte(theRow["EMG_CONTACT_ID"]);
                        _ProspectEmgContactInfo.EMG_CONTACT_NAME = theRow["EMG_CONTACT_NAME"].ToString();
                        _ProspectEmgContactInfo.EMG_RELATIONSHIP = theRow["EMG_RELATIONSHIP"].ToString();
                        _ProspectEmgContactInfo.EMG_PHONE_1 = theRow["EMG_PHONE_1"].ToString();
                        _ProspectEmgContactInfo.EMG_PHONE_2 = theRow["EMG_PHONE_2"].ToString();
                        _ProspectEmgContactInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectEmgContactInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectEmgContactInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _ProspectEmgContactInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _ProspectEmgContactInfo.UPLOAD_BY = UploadBy;
                        _ProspectEmgContactInfo.UPLOAD_DATE = UploadDate;

                        _Prospect.SaveProspectEmgContact(_ProspectEmgContactInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Emg Contact " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_VISIT(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Shuba.Model.ProspectVisitInfo _ProspectVisitInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectVisitInfo = new Shuba.Model.ProspectVisitInfo();

                        _ProspectVisitInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _ProspectVisitInfo.VISIT_NO = Convert.ToByte(theRow["VISIT_NO"]);
                        _ProspectVisitInfo.OBJECTIVE_OF_VISIT = theRow["OBJECTIVE_OF_VISIT"].ToString();
                        _ProspectVisitInfo.DATE_VISIT_PLAN = Convert.ToDateTime(theRow["DATE_VISIT_PLAN"]);
                        _ProspectVisitInfo.DATE_VISIT_ACTUAL = Convert.ToDateTime(theRow["DATE_VISIT_ACTUAL"]);
                        _ProspectVisitInfo.JOINT_FIELD_WORK = theRow["JOINT_FIELD_WORK"].ToString();
                        _ProspectVisitInfo.RESULT_OF_VISIT = theRow["RESULT_OF_VISIT"].ToString();
                        _ProspectVisitInfo.REMARK = theRow["REMARK"].ToString();
                        _ProspectVisitInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectVisitInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectVisitInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _ProspectVisitInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _ProspectVisitInfo.UPLOAD_BY = UploadBy;
                        _ProspectVisitInfo.UPLOAD_DATE = UploadDate;

                        _Prospect.SaveProspectVisit(_ProspectVisitInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Visit " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_MAINTENANCE_VISIT(DataSet MyDataSet, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Shuba.Model.MaintenanceVisitInfo _MaintenanceVisitInfo;
                Shuba.Business.MaintenanceVisit _MaintenanceVisit = new Shuba.Business.MaintenanceVisit();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _MaintenanceVisitInfo = new Shuba.Model.MaintenanceVisitInfo();

                        _MaintenanceVisitInfo.VISIT_ID = theRow["VISIT_ID"].ToString();
                        _MaintenanceVisitInfo.PROSPECT_ID = theRow["PROSPECT_ID"].ToString();
                        _MaintenanceVisitInfo.AO_CODE = theRow["AO_CODE"].ToString();
                        _MaintenanceVisitInfo.UNIT_CODE = theRow["UNIT_CODE"].ToString();
                        _MaintenanceVisitInfo.DATE_VISIT = Convert.ToDateTime(theRow["DATE_VISIT"]);
                        _MaintenanceVisitInfo.NO_ACCOUNT = theRow["NO_ACCOUNT"].ToString();
                        _MaintenanceVisitInfo.DEBTOR_NAME = theRow["DEBTOR_NAME"].ToString();
                        _MaintenanceVisitInfo.OBJECTIVE_OF_VISIT = theRow["OBJECTIVE_OF_VISIT"].ToString();
                        _MaintenanceVisitInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _MaintenanceVisitInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _MaintenanceVisitInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _MaintenanceVisitInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _MaintenanceVisitInfo.IS_DELETED = theRow["IS_DELETED"].ToString();

                        _MaintenanceVisit.Save(_MaintenanceVisitInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Maintenance Visit " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_SO_TARGET_PLAN(DataSet MyDataSet, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Shuba.Model.SOTargetPlanInfo _SOTargetPlanInfo;
                Shuba.Business.SOTargetPlan _SOTargetPlan = new Shuba.Business.SOTargetPlan();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _SOTargetPlanInfo = new Shuba.Model.SOTargetPlanInfo();

                        _SOTargetPlanInfo.TARGET_YEAR = theRow["TARGET_YEAR"].ToString();
                        _SOTargetPlanInfo.TARGET_MONTH = theRow["TARGET_MONTH"].ToString();
                        _SOTargetPlanInfo.AO_CODE = theRow["AO_CODE"].ToString();
                        _SOTargetPlanInfo.UNIT_CODE = theRow["UNIT_CODE"].ToString();
                        _SOTargetPlanInfo.TARGET_AMOUNT = Convert.ToDouble(theRow["TARGET_AMOUNT"]);
                        _SOTargetPlanInfo.NOA = Convert.ToInt32(theRow["NOA"]);
                        _SOTargetPlanInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _SOTargetPlanInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _SOTargetPlanInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _SOTargetPlanInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _SOTargetPlanInfo.IS_DELETED = theRow["IS_DELETED"].ToString();

                        _SOTargetPlan.SaveSOTargetPlan(_SOTargetPlanInfo);

                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload SO Target Plan " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public string UPLOAD_TBL_PROSPECT_REFFERAL(DataSet MyDataSet, string UploadBy, DateTime UploadDate, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {

                Shuba.Model.ProspectRefferalInfo _ProspectRefferalInfo;
                Shuba.Business.Prospect _Prospect = new Shuba.Business.Prospect();

                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        _ProspectRefferalInfo = new Shuba.Model.ProspectRefferalInfo();

                        _ProspectRefferalInfo.REFFERAL_ID = theRow["REFFERAL_ID"].ToString();
                        _ProspectRefferalInfo.FULL_NAME = theRow["FULL_NAME"].ToString();
                        _ProspectRefferalInfo.JENIS_USAHA = theRow["JENIS_USAHA"].ToString();
                        _ProspectRefferalInfo.OFFICE_PHONE = theRow["OFFICE_PHONE"].ToString();
                        _ProspectRefferalInfo.RENCANA_PLAFON = Convert.ToDouble(theRow["RENCANA_PLAFON"]);
                        _ProspectRefferalInfo.ADDRESS_BUSINESS = theRow["ADDRESS_BUSINESS"].ToString();
                        _ProspectRefferalInfo.UNIT_CODE_SOURCE = theRow["UNIT_CODE_SOURCE"].ToString();
                        _ProspectRefferalInfo.UNIT_CODE_DEST = theRow["UNIT_CODE_DEST"].ToString();
                        _ProspectRefferalInfo.AO_CODE_ORIG = theRow["AO_CODE_ORIG"].ToString();
                        _ProspectRefferalInfo.ENTRY_BY = theRow["ENTRY_BY"].ToString();
                        _ProspectRefferalInfo.ENTRY_DATE = Convert.ToDateTime(theRow["ENTRY_DATE"]);
                        _ProspectRefferalInfo.UPDATE_BY = theRow["UPDATE_BY"].ToString();
                        _ProspectRefferalInfo.UPDATE_DATE = Convert.ToDateTime(theRow["UPDATE_DATE"]);
                        _ProspectRefferalInfo.UPLOAD_BY = UploadBy; // theRow["UPLOAD_BY"].ToString();
                        _ProspectRefferalInfo.UPLOAD_DATE = UploadDate; //Convert.ToDateTime(theRow["UPLOAD_DATE"]);
                        _ProspectRefferalInfo.IS_DOWNLOADED = "0";
                        _ProspectRefferalInfo.DOWNLOAD_BY = "";// UploadBy;
                        _ProspectRefferalInfo.DOWNLOAD_DATE = Convert.ToDateTime("01-01-2000"); //UploadDate;

                        _Prospect.SaveProspectRefferal(_ProspectRefferalInfo);
                    }
                    return "";
                }
                catch (Exception ex)
                {
                    return "Fungsi Upload Prospect Refferal " + ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
        }

        [WebMethod]
        public bool CheckService()
        {
            return true;
        }

        ////FOR OLD SPADA
        //[WebMethod]
        //public string GeneratePRN(string unitCode, string segmentCode, string prospectType, string CustName, string IsNegativeChecking)
        //{
        //    return "";

        //    SqlConnection sqlCon = null;
        //    SqlCommand sqlCM = null;

        //    try
        //    {
        //        sqlCon = new SqlConnection(ConnStr());
        //        if (sqlCon.State == ConnectionState.Closed)
        //        {
        //            sqlCon.Open();
        //        }
        //        sqlCM = new SqlCommand("GENERATE_PRN", sqlCon);
        //        sqlCM.CommandType = CommandType.StoredProcedure;
        //        SqlParameter paramUnitCode = new SqlParameter("@UNIT_CODE", SqlDbType.VarChar) { Value = unitCode };
        //        sqlCM.Parameters.Add(paramUnitCode);

        //        SqlParameter paramSegmentCode = new SqlParameter("@SEGMENT_CODE", SqlDbType.VarChar) { Value = segmentCode };
        //        sqlCM.Parameters.Add(paramSegmentCode);

        //        SqlParameter paramProspectType = new SqlParameter("@PROSPECT_TYPE", SqlDbType.VarChar) { Value = prospectType };
        //        sqlCM.Parameters.Add(paramProspectType);

        //        SqlParameter paramCustName = new SqlParameter("@NAM_CUST", SqlDbType.VarChar) { Value = CustName };
        //        sqlCM.Parameters.Add(paramCustName);

        //        SqlParameter paramIsNegativeCheck = new SqlParameter("@IS_CHECK_NEGATIVE_FILE", SqlDbType.VarChar) { Value = IsNegativeChecking.Trim() };
        //        sqlCM.Parameters.Add(paramIsNegativeCheck);

        //        return sqlCM.ExecuteScalar().ToString();
        //    }
        //    catch
        //    {
        //        return string.Empty;
        //    }
        //    finally
        //    {
        //        if (sqlCM != null)
        //        {
        //            sqlCM.Dispose();
        //        }
        //        if (sqlCon != null)
        //        {
        //            sqlCon.Close();
        //            sqlCon.Dispose();
        //        }
        //    }
        //}
    }
}